var mysql = require('mysql');
var async = require("async");
var dbconfig = require('../config/database.js');
var connection = mysql.createConnection(dbconfig.connection);
var connection2 = mysql.createConnection(dbconfig.connection);

var pool = mysql.createPool({
    host: dbconfig.connection.host,
    user: dbconfig.connection.user,
    password: dbconfig.connection.password,
    database: dbconfig.database_sales,
    connectionLimit: 10,
    // port: "mysql db port",
    waitForConnections: false
});

connection.query('USE ' + dbconfig.database);
connection2.query('USE ' + dbconfig.database_sales);

// app/routes.js
module.exports = function(app, passport) {

	// =====================================
	// HOME PAGE (with login links) ========
	// =====================================
	app.get('/', function(req, res) {
		res.render('login.ejs', { message: req.flash('loginMessage') }); // load the index.ejs file
	});

	// =====================================
	// LOGIN ===============================
	// =====================================
	// show the login form
	app.get('/login', function(req, res) {

		// render the page and pass in any flash data if it exists
		res.render('login.ejs', { message: req.flash('loginMessage') });
	});

	// process the login form
	app.post('/login', passport.authenticate('local-login', {
            successRedirect : '/a_index', // redirect to the secure companyMan section
            failureRedirect : '/login', // redirect back to the signup page if there is an error
            failureFlash : true // allow flash messages
		}),
        function(req, res) {
            if (req.body.remember) {
              	req.session.cookie.maxAge = 1000 * 60 * 3;
            } else {
              	req.session.cookie.expires = false;
            }
        res.redirect('/');
    });

	// =====================================
	// SIGNUP ==============================
	// =====================================
	// show the signup form
	app.get('/signup', function(req, res) {
		// render the page and pass in any flash data if it exists
		res.render('signup.ejs', { message: req.flash('signupMessage') });
	});

	// process the signup form
	app.post('/signup', passport.authenticate('local-signup', {
		successRedirect : '/a_index', // redirect to the secure companyMan section
		failureRedirect : '/signup', // redirect back to the signup page if there is an error
		failureFlash : true // allow flash messages
	}));

	// 跳转到商户资料界面
	app.get('/companyMan', isLoggedIn, function(req, res) {
		res.render('companyMan.ejs', {});
	});

	// 跳转到提成明显界面
	app.get('/bonusDetail', isLoggedIn, function(req, res) {
		res.render('bonusDetail.ejs', {});
	});

	// 跳转到商户密钥界面
	app.get('/companyKeyt', isLoggedIn, function(req, res) {
		res.render('companyKeyt.ejs', {});
	});

	/* ---------------------  商户资料  --------------------------*/
		// 获取商户资料接口
		app.get('/getCompanyData', isLoggedIn, function(req, res) {

			connection.query("SELECT * FROM "+dbconfig.store_profile_table + " ORDER BY status desc,add_time DESC", function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					var curCompanyData;
					if ( req.query.curPage ) {
						curCompanyData = rows.slice((req.query.curPage-1)*10, req.query.curPage*10)
					} else {
						curCompanyData = rows.slice(0, 10);
					}
					res.send({
						data: curCompanyData,
						curPage: parseInt(req.query.curPage),
						totalElements: rows.length,
						totalPages: Math.ceil(rows.length/10)
					});
				}
			});
		});

		

		// 处理商户
		app.put('/dealWith', isLoggedIn, function(req, res) {
			connection.query("UPDATE " + dbconfig.store_profile_table + " SET status=\'" + req.body.status + "\' WHERE id=\'" + req.body.id + "\'", function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data:''
					});
				}
			});
		});

		// 搜索商户
		app.get('/search', isLoggedIn, function(req, res) {
			connection.query("SELECT * FROM " + dbconfig.store_profile_table + " WHERE store_abbr_name like \'%"+req.query.keywords+"%\' or contact_mobile like \'%"+req.query.keywords+"%\' limit 10", function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data:rows,
						curPage: 1,
						totalElements: rows.length,
						totalPages: 1
					});
				}
			});
		});


	/* ---------------------  提成明显  --------------------------*/
		// 获取商户资料接口
		app.get('/getProfileData', isLoggedIn, function(req, res) {
			connection2.query('SELECT a.*,b.`amount_total` ,b.`surplus_amount`   FROM `'+dbconfig.db2_profile+'` a LEFT JOIN `'+dbconfig.db2_account+'` b on (a.`username`=b.`username` ) where a.`user_type_enums` in ("ONE","TWO") ORDER BY b.`surplus_amount` DESC', function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {

					var curProfileData;
					if ( req.query.curPage ) {
						curProfileData = rows.slice((req.query.curPage-1)*10, req.query.curPage*10)
					} else {
						curProfileData = rows.slice(0, 10);
					}

					res.send({
						data: curProfileData,
						curPage: parseInt(req.query.curPage),
						totalElements: rows.length,
						totalPages: Math.ceil(rows.length/10)
					});
				}
			});
		});

		// 获取提成记录
		app.get('/getTicheng', isLoggedIn, function(req, res) {
			connection2.query('SELECT * FROM '+dbconfig.db2_commission_details+' where username="'+req.query.username+'" order by update_time desc', function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {

					var curProfileData;
					if ( req.query.curPage ) {
						curProfileData = rows.slice((req.query.curPage-1)*7, req.query.curPage*7)
					} else {
						curProfileData = rows.slice(0, 7);
					}

					res.send({
						data: curProfileData,
						curPage: parseInt(req.query.curPage),
						totalElements: rows.length,
						totalPages: Math.ceil(rows.length/7)
					});
				}
			});
		});

		// 获取打款记录
		app.get('/getDakuan', isLoggedIn, function(req, res) {
			connection2.query('SELECT * FROM '+dbconfig.db2_cash_record+' where username="'+req.query.username+'" order by update_time desc', function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {

					var curProfileData;
					if ( req.query.curPage ) {
						curProfileData = rows.slice((req.query.curPage-1)*7, req.query.curPage*7)
					} else {
						curProfileData = rows.slice(0, 7);
					}

					res.send({
						data: curProfileData,
						curPage: parseInt(req.query.curPage),
						totalElements: rows.length,
						totalPages: Math.ceil(rows.length/7)
					});
				}
			});
		});

		app.get('/getUser', isLoggedIn, function(req, res) {
			connection2.query('SELECT * FROM '+dbconfig.db2_profile+' where username="'+req.query.username+'"', function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data: rows,
					});
				}
			});
		});

		app.get('/getCurAccount', isLoggedIn, function(req, res) {
			connection2.query('SELECT a.*,b.`amount_total` ,b.`surplus_amount`   FROM `'+dbconfig.db2_profile+'` a LEFT JOIN `'+dbconfig.db2_account+'` b on (a.`username`=b.`username` ) where a.`username`='+req.query.username+' ORDER BY b.`surplus_amount` DESC', function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data: rows,
					});
				}
			});
		});


		// 完成打款
		app.put('/playMoney', isLoggedIn, function(req, res) {
			var sqlParamsEntity = [];
			sqlParamsEntity.push({
		        sql: "update "+dbconfig.db2_account+" set surplus_amount=surplus_amount-"+req.query.surplus_amount+" where username='"+req.query.username+"'",
		    });
		    sqlParamsEntity.push({
		        sql: "INSERT INTO `"+dbconfig.database_sales+"`.`"+dbconfig.db2_cash_record+"` (`id`, `amount`, `username`) VALUES ('"+createUUid()+"', '"+req.query.surplus_amount+"', '"+req.query.username+" ')",
		    });

			execTrans(sqlParamsEntity, function(err, info) {
			    if (err) {
			        console.error("事务执行失败");
			    } else {
			        console.log("done.");

			        res.send({});
			    }
			})


		});


	/* ---------------------  商户密钥  --------------------------*/
		// 搜索商户
		app.get('/searchKey', isLoggedIn, function(req, res) {
			connection.query("SELECT * FROM " + dbconfig.store_table + " WHERE name like \'%"+req.query.keywords+"%\' or username like \'%"+req.query.keywords+"%\' limit 10", function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data:rows,
						curPage: 1,
						totalElements: rows.length,
						totalPages: 1
					});
				}
			});
		});
		// 修改等级和密钥
		app.put('/changeLevel', isLoggedIn, function(req, res) {
			connection.query("UPDATE " + dbconfig.store_table + " SET level=\'" + req.body.level +"\',`key`=\'"+req.body.key+"\',`expire_time`=\'" + req.body.expire_time + "\',mch_id=\'" + req.body.mch_id + "\' WHERE id=\'" + req.body.id + "\'", function(err, rows) {
				if ( err ) {
					res.redirect('/');
				} else {
					res.send({
						data:''
					});
				}
			});
		});


	/* ---------------------  总接口  --------------------------*/
		app.get('/a_index', isLoggedIn, function(req, res) {
			res.render('a_index.ejs', {
				user : req.user
			});
		});

		// =====================================
		// LOGOUT ==============================
		// =====================================
		app.get('/logout', function(req, res) {
			req.logout();
			res.redirect('/');
		});
};

// route middleware to make sure
function isLoggedIn(req, res, next) {
	// if user is authenticated in the session, carry on
	if (req.isAuthenticated()) {
		return next();
	}

	// if they aren't redirect them to the home page
	res.redirect('/');
}

/*var pool = mysql.createPool({
    host: "mysql host",
    user: "mysql login user",
    password: "mysql login pwd",
    database: "target db name",
    connectionLimit: 10,
    port: "mysql db port",
    waitForConnections: false
});*/

function _getNewSqlParamEntity(sql, callback) {
    if (callback) {
        return callback(null, {
            sql: sql,
        });
    }
    return {
        sql: sql,
    };
}


function execTrans(sqlparamsEntities, callback) {
    pool.getConnection(function(err, connection) {
    	if (err) {
	      	return callback(err, null);
	    }
        connection.beginTransaction(function(err) {
            if (err) {
                return callback(err, null);
            }
            console.log("开始执行transaction，共执行" + sqlparamsEntities.length + "条数据");
            var funcAry = [];
            sqlparamsEntities.forEach(function(sql_param) {
                var temp = function(cb) {
                    var sql = sql_param.sql;
                    var param = sql_param.params;
                    connection.query(sql, param, function(tErr, rows, fields) {
                        if (tErr) {
                            connection.rollback(function() {
                                console.log("事务失败，" + sql_param + "，ERROR：" + tErr);
                                throw tErr;
                            });
                        } else {
                            return cb(null, 'ok');
                        }
                    })
                };
                funcAry.push(temp);
            });

            async.series(funcAry, function(err, result) {
                console.log("transaction error: " + err);
                if (err) {
                    connection.rollback(function(err) {
                        console.log("transaction error: " + err);
                        connection.release();
                        return callback(err, null);
                    });
                } else {
                    connection.commit(function(err, info) {
                        console.log("transaction info: " + JSON.stringify(info));
                        if (err) {
                            console.log("执行事务失败，" + err);
                            connection.rollback(function(err) {
                                console.log("transaction error: " + err);
                                connection.release();
                                return callback(err, null);
                            });
                        } else {
                            connection.release();
                            return callback(null, info);
                        }
                    })
                }
            })
        });
    });
}

function createUUid ( len, radix ) {
    var chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'.split('');
    var uuid = [], i;
    radix = radix || chars.length;
    if (len) {
        // Compact form
        for (i = 0; i < len; i++) uuid[i] = chars[0 | Math.random()*radix];
    } else {
        // rfc4122, version 4 form
        var r;
 
        // rfc4122 requires these characters
        uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-';
        uuid[14] = '4';
 
        // Fill in random data.  At i==19 set the high bits of clock sequence as
        // per rfc4122, sec. 4.1.5
        for ( i = 0; i < 32; i++ ) {
            if ( !uuid[i] ) {
                r = 0 | Math.random()*63;
                uuid[i] = chars[(i == 19) ? (r & 0x3) | 0x8 : r];
            }
        }
    }

    return uuid.join('');
}